﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ImmovablesManager
{
    public class ImmovablesRepository
    {
        private z257130_immovablesEntities _imEntites;
        private const string CONNECTION_STRING = @"metadata=res://*/ImmovablesDBDataModel.csdl|res://*/ImmovablesDBDataModel.ssdl|res://*/ImmovablesDBDataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=mssql01.infobox.ru;initial catalog=z257130_immovables;persist security info=True;user id=z257130_admin;password=1q2w3e4r;multipleactiveresultsets=True;App=EntityFramework&quot;";
        public ImmovablesRepository()
        {
            _imEntites = new z257130_immovablesEntities();
        }

        public bool HasRealtorFinancialInfo(Guid realtorId)
        {
            var currentFinancialData = _imEntites.FinancialData.Where(data => data.FinancialDataRealtorId == realtorId);
            if (currentFinancialData.Count() > 0)
            {
                return true;
            }
            return false;

        }

        public void RemoveRealtor(Guid realtorGuid)
        {
            _imEntites.Realtor.DeleteObject(GetRealtorById(realtorGuid));
            _imEntites.SaveChanges();
        }


        #region ADD

        public Guid AddUser(string userfirstName, 
            string userlastName, 
            string userthirdName, 
            string userLogin, 
            string userPassword,
            string userPhoneNumber, 
            string userEmail, 
            string userRole)
        {
            User newUser = new User();
            newUser.UserPassword = userPassword;
            newUser.UserFirstName = userfirstName;
            newUser.UserEmail = userEmail;
            newUser.UserLastName = userlastName;
            newUser.UserLogin = userLogin;
            newUser.UserPhoneNumber = userPhoneNumber;
            newUser.UserRole = newUser.UserRole;

            _imEntites.User.AddObject(newUser);
            _imEntites.SaveChanges();

            return newUser.UserId;
        }



        public Guid AddFlat(string flatAdditionalInformation,
            Guid flatBalconId, string flatAddress, string flatBoilerPosition, string flatDocumentsReady,
            Guid flatFinancialDataId, int? flatFloor, int? flatFloors, Guid flatHouseMaterial, double? flatKitchenArea,
            double? flatTotalArea, double? flatLivingArea, string flatObject, Guid flatRegionId, bool? flatMortgage,
            string flatNearestSigns, Guid flatOwnerId, string flatNote, int? flatRoomCount, int? flatRoomsToSell,
            Guid flatSellTermsId, int? flatNeighboursCount, int? flatNeighboursChildrenCount, int? flatNeighboursPensioneersCount,
            string flatShowTerms, DateTime? flatShowTime, bool? flatTelephone, Guid flatTypeId, double? flatTotalCost, double? flatCostPerMeter,
            Guid flatWCId, string flatIRR, string flatWindowsView, string flatHowToGetByPubTransport,
            bool? FlatRoomAppointment, string flatRoomPlanning, bool isShown)
        {
            var flat = new Flat()
                           {
                               FlatAdditionalInformation = flatAdditionalInformation,
                               FlatBalconId = flatBalconId,
                               FlatAddress = flatAddress,
                               FlatBoilerPosition = flatBoilerPosition,
                               FlatDocumentsReady = flatDocumentsReady,
                               FlatFinancialDataId = flatFinancialDataId,
                               FlatFloor = flatFloor,
                               FlatFloors = flatFloors,
                               FlatHouseMaterial = flatHouseMaterial,
                               FlatKitchenArea = flatKitchenArea,
                               FlatTotalArea = flatTotalArea,
                               FlatLivingArea = flatLivingArea,
                               FlatObject = flatObject,
                               FlatRegionId = flatRegionId,
                               FlatMortgage = flatMortgage,
                               FlatNearestSigns = flatNearestSigns,
                               FlatOwnerId = flatOwnerId,
                               FlatNote = flatNote,
                               FlatRoomCount = flatRoomCount,
                               FlatRoomsToSell = flatRoomsToSell,
                               FlatSellTermsId = flatSellTermsId,
                               FlatNeighboursCount = flatNeighboursCount,
                               FlatNeighboursChildrenCount = flatNeighboursChildrenCount,
                               FlatNeighboursPensioneersCount = flatNeighboursPensioneersCount,
                               FlatShowTerms = flatShowTerms,
                               FlatShowTime = flatShowTime,
                               FlatTelephone = flatTelephone,
                               FlatTypeId = flatTypeId,
                               FlatTotalCost = flatTotalCost,
                               FlatCostPerMeter = flatCostPerMeter,
                               FlatWCId = flatWCId,
                               FlatIRR = flatIRR,
                               FlatWindowsView = flatWindowsView,
                               FlatHowToGetByPubTransport = flatHowToGetByPubTransport,
                               FlatRoomAppointment = FlatRoomAppointment,
                               FlatRoomPlanning = flatRoomPlanning,
                               FlatIsShown = isShown
                               
                           };
            _imEntites.AddToFlat(flat);
            _imEntites.SaveChanges();
            return flat.FlatId;
        }


        public  Guid AddNewFlat(string newFlatAdditionalInfo, string newFlatAddress, Guid newFlatBalconTypeId,
            string newFlatBuisinessFinishedDate, double? newFlatCostPerMeter, string newFlatDocuments, string newFlatFacingAdditionalInfo,
            int? newFlatFloor, int? newFlatFloorsCount, Guid newFlatFinancialDataId, double? newFlatTotalArea,
            double? newFlatLivingArea, double? newFlatKitchenArea, Guid newFlatHouseMaterialId, string newFlatObject,
            string newFlatObjectBuilder, string newFlatNote, Guid newFlatOwnerId, int? newFlatRoomsCount, string newFlatSellForm,
            string newFlatShowTerms, DateTime? newFlatShowTime, string newFlatBuildingStage, Guid newFlatTypeId, string newFlatIRR,
            Guid newFlatWCId, string newFlatWindowsView, string newFlatInfoForContactsWithSellers, Guid newFlatObjectRegionId,
            string newFlatObjectNearestSign, double? newFlatTotalCost, bool? newFlatFacingTypeBlack, string newFlatFacing, bool? newFlatAjoiningRoom, 
            bool isShown)
        {
            var newFlat = new NewFlat()
                              {
                                  NewFlatAdditionalInfo = newFlatAdditionalInfo,
                                  NewFlatAddress = newFlatAddress,
                                  NewFlatBalconTypeId = newFlatBalconTypeId,
                                  NewFlatBuildingFinishedDate = newFlatBuisinessFinishedDate,
                                  NewFlatCostPerMeter = newFlatCostPerMeter,
                                  NewFlatDocuments = newFlatDocuments,
                                  NewFlatFacingAdditionalInfo = newFlatFacingAdditionalInfo,
                                  NewFlatFloor = newFlatFloor,
                                  NewFlatFloorsCount = newFlatFloorsCount,
                                  NewFlatFinancialDataId = newFlatFinancialDataId,
                                  NewFlatTotalArea = newFlatTotalArea,
                                  NewFlatLivingArea = newFlatLivingArea,
                                  NewFlatKitchenArea = newFlatKitchenArea,
                                  NewFlatHouseMaterialId = newFlatHouseMaterialId,
                                  NewFlatObject = newFlatObject,
                                  NewFlatObjectBuilder = newFlatObjectBuilder,
                                  NewFlatNote = newFlatNote,
                                  NewFlatOwnerId = newFlatOwnerId,
                                  NewFlatRoomsCount = newFlatRoomsCount,
                                  NewFlatSellForm = newFlatSellForm,
                                  ////исправить
                                  NewFlatShowTerms = newFlatShowTerms,
                                  NewFlatShowTime = newFlatShowTime,
                                  NewFlatBuildingStage = newFlatBuildingStage,
                                  NewFlatTypeId = newFlatTypeId,
                                  NewFlatIRR = newFlatIRR,
                                  NewFlatWCId = newFlatWCId,
                                  NewFlatWindowsView = newFlatWindowsView,
                                  NewFlatInfoForContactsWithSellers = newFlatInfoForContactsWithSellers,
                                  NewFlatObjectRegionId = newFlatObjectRegionId,
                                  NewFlatObjectNearestSign = newFlatObjectNearestSign,
                                  NewFlatTotalCost = newFlatTotalCost,
                                  NewFlatFacingTypeBlack = newFlatFacingTypeBlack,
                                  //отделка
                                  NewFlatFacing = newFlatFacing,
                                  NewFlatAjoiningRoom = newFlatAjoiningRoom,
                                  NewFlatIsShown = isShown
                              };
            _imEntites.AddToNewFlat(newFlat);
            _imEntites.SaveChanges();
            return newFlat.NewFlatId;

        }

        public Guid AddCommercialRealEstate(string commercialRealEstateAdditionalInformation, string commercialRealEstateAddress,
            bool? commercialRealEstateBaseFloor, Guid commercialRealEstateFinancialDataId, int? commercialRealEstateFloor, int? commercialRealEstateMaxFloors,
            Guid commercialRealEstateFloorMaterialId, bool? commercialRealEstateInternet, double? commercialRealEstateCellingHeigh,
            string commercialRealEstateEntrance, bool? commercialRealEstateEntranceSeparate, string commercialRealEstateObject,
            bool? commercialRealEstateHasTenants, string commercialRealEstateTenants, int? commercialRealEstateLineFromCentralStreet, DateTime? commercialRealEstatePutPeriodFrom,
            bool? commercialRealEstatePhone, string commercialRealEstateGeneralInformation, string commercialRealEstateCommunications, Guid commercialRealEstateOwnerId,
            Guid commercialRealEstateWCId, string commercialRealEstatePowers, Guid commercialRealEstateParkingTypeId, string commercialRealEstateRepair,
            int? commercialRealEstateRoomCount, Guid commercialRealEstateRegionId, string commercialRealEstateShowTerms, DateTime? commercialRealEstateShowTime,
            Guid commercialRealEstateWallMaterialId, double? commercialRealEstateTotalArea, string commercialRealEstatePremisesLocation,
            string commercialRealEstateWindows, DateTime? commercialRealEstatePutPeriodTo, Guid commercialRealEstateTypeId, string commercialRealEstatePlanning,
            string commercialRealEstateAppointment, bool isShown)
        {
            var commercialRealEstate = new CommercialRealEstate()
                                           {
                                               CommercialRealEstateAdditionalInformation =
                                                   commercialRealEstateAdditionalInformation,
                                               CommercialRealEstateAddress = commercialRealEstateAddress,
                                               CommercialRealEstateBaseFloor = commercialRealEstateBaseFloor,
                                               CommercialRealEstateFinancialDataId = commercialRealEstateFinancialDataId,
                                               CommercialRealEstateFloor = commercialRealEstateFloor,
                                               CommercialRealEstateMaxFloors = commercialRealEstateMaxFloors,
                                               CommercialRealEstateFloorMaterialId = commercialRealEstateFloorMaterialId,
                                               CommercialRealEstateInternet = commercialRealEstateInternet,
                                               CommercialRealEstateCellingHeigh = commercialRealEstateCellingHeigh,
                                               CommercialRealEstateEntrance = commercialRealEstateEntrance,
                                               CommercialRealEstateEntranceSeparate =
                                                   commercialRealEstateEntranceSeparate,
                                               CommercialRealEstateObject = commercialRealEstateObject,
                                               CommercialRealEstateHasTenants = commercialRealEstateHasTenants,
                                               CommercialRealEstateTenants = commercialRealEstateTenants,
                                               CommercialRealEstateLineFromCentralStreet =
                                                   commercialRealEstateLineFromCentralStreet,
                                               CommercialRealEstatePutPeriodFrom = commercialRealEstatePutPeriodFrom,
                                               CommercialRealEstatePhone = commercialRealEstatePhone,
                                               CommercialRealEstateGeneralInformation =
                                                   commercialRealEstateGeneralInformation,
                                               CommercialRealEstateCommunications = commercialRealEstateCommunications,
                                               CommercialRealEstateOwnerId = commercialRealEstateOwnerId,
                                               CommercialRealEstateWCId = commercialRealEstateWCId,
                                               CommercialRealEstatePowers = commercialRealEstatePowers,
                                               CommercialRealEstateParkingTypeId = commercialRealEstateParkingTypeId,
                                               CommercialRealEstateRepair = commercialRealEstateRepair,
                                               CommercialRealEstateRoomCount = commercialRealEstateRoomCount,
                                               CommercialRealEstateRegionId = commercialRealEstateRegionId,
                                               CommercialRealEstateShowTerms = commercialRealEstateShowTerms,
                                               CommercialRealEstateShowTime = commercialRealEstateShowTime,
                                               CommercialRealEstateWallMaterialId = commercialRealEstateWallMaterialId,
                                               CommercialRealEstateTotalArea = commercialRealEstateTotalArea,
                                               CommercialRealEstatePremisesLocation =
                                                   commercialRealEstatePremisesLocation,
                                               CommercialRealEstateWindows = commercialRealEstateWindows,
                                               CommercialRealEstatePutPeriodTo = commercialRealEstatePutPeriodTo,
                                               CommercialRealEstateTypeId = commercialRealEstateTypeId,
                                               CommercialRealEstatePlanning = commercialRealEstatePlanning,
                                               CommercialRealEstateAppointment = commercialRealEstateAppointment,
                                               CommercialRealEstateIsShown = isShown
                }; 
            _imEntites.AddToCommercialRealEstate(commercialRealEstate);
            _imEntites.SaveChanges();
            return commercialRealEstate.CommercialRealEstatetId;
        }

        public Guid AddPlot(string plotsAdditionalInformation, string plotsAddress, double? plotsArea, string plotsBuildingRights,
            bool? plotsCommunicationElectricPower, bool? plotsCommunicationGasSupply, bool? plotsCommunicationSewage, bool? plotsCommunicationWater,
            double? plotsDistanceFromBusStop, double? plotsDistanceFromForest, double? plotsDistanceFromLake, double plotsDistanceFromRailwayStation,
            Guid plotsFinancialDataId, string plotsDocumentsReady, string plotsNearestSign, string plotsObject, Guid plotsOwnerId, Guid plotsRegionId,
            bool? plotsRegistration, string plotsPurpose, bool? plotsMastered, DateTime? plotsShowDate, Guid plotsSellTermsId, string plotsHowToGetByTransport,
            string plotsShowTerms, bool isShown)
        {
            var plots = new Plots()
                            {
                                PlotsAdditionalInformation = plotsAdditionalInformation,
                                PlotsAddress = plotsAddress,
                                PlotsArea = plotsArea,
                                PlotsBuildingRights = plotsBuildingRights,
                                PlotsCommunicationElectricPower = plotsCommunicationElectricPower,
                                PlotsCommunicationGasSupply = plotsCommunicationGasSupply,
                                PlotsCommunicationSewage = plotsCommunicationSewage,
                                PlotsCommunicationWater = plotsCommunicationWater,
                                PlotsDistanceFromBusStop = plotsDistanceFromBusStop,
                                PlotsDistanceFromForest = plotsDistanceFromForest,
                                PlotsDistanceFromLake = plotsDistanceFromLake,
                                PlotsDistanceFromRailwayStation = plotsDistanceFromRailwayStation,
                                PlotsFinancialDataId = plotsFinancialDataId,
                                PlotsDocumentsReady = plotsDocumentsReady,
                                PlotsNearestSign = plotsNearestSign,
                                PlotsObject = plotsObject,
                                PlotsOwnerId = plotsOwnerId,
                                PlotsRegionId = plotsRegionId,
                                PlotsRegistration = plotsRegistration,
                                PlotsPurpose = plotsPurpose,
                                PlotsMastered = plotsMastered,
                                PlotsShowDate = plotsShowDate,
                                PlotsSellTermsId = plotsSellTermsId,
                                PlotsHowToGetByTransport = plotsHowToGetByTransport,
                                PlotsShowTerms = plotsShowTerms,
                                PlotsIsShown = isShown
                            };
            _imEntites.AddToPlots(plots);
            _imEntites.SaveChanges();
            return plots.PlotsId;
        }

        public Guid AddOwner(string ownerHomePhone, string ownerMobilePhone, string ownerName)
        {
            var owner = new Owner()
                            {
                                OwnerHomePhone = ownerHomePhone,
                                OwnerMobilePhone = ownerMobilePhone,
                                OwnerName = ownerName
                            };
            _imEntites.AddToOwner(owner);
            _imEntites.SaveChanges();
            return owner.OwnerId;
        }


   public Guid AddRealtor(string realtorFirstName, string realtorLastName, string realtorThirdName, string realtorPhone, string realtorAdditionalPhone, string realtorAdvName,
   string realtorEmail, string realtorUserName, string realtorPassword)
        {
            var realtor = new Realtor()
            {
                RealtorFirstName = realtorFirstName,
                RealtorLastName = realtorLastName,
                RealtorAdditionalPhone = realtorAdditionalPhone,
                RealtorAdvName = realtorAdvName,
                RealtorEmail = realtorEmail,
                RealtorPassword = realtorPassword,
                RealtorPhone = realtorPhone,
                RealtorThirdName = realtorThirdName,
                RealtorUserName = realtorUserName
            };
            _imEntites.AddToRealtor(realtor);
            _imEntites.SaveChanges();
            return realtor.RealtorId;
        }



        //public string GetUserRoleBuUserPassword(Guid userId)
        //{
        //    var result = _imEntites.User.Where(user => user.UserLogin == login & user.UserPassword == password);
        //    if (result.Count() == 0)
        //    {
        //        return false;
        //    }
        //    return true;
        //}

        public User GetUserByLoginPassword(string login, string password)
        {

            return _imEntites.User.ToList().Find(user => user.UserLogin == login & user.UserPassword == password);
        }

        public IEnumerable<ChangingStory> GetAllStories()
        {
            return _imEntites.ChangingStory.AsEnumerable();
        }

        public Guid AddChangingStory(string changingOperation, Guid chaginggStoryCardId, string changingValue, string changingOldValue, Guid userId, string state, string numberCard)
        {
            var story = new ChangingStory();

            story.ChangingOperation = changingOperation;
            story.ChaginggStoryCardId = chaginggStoryCardId;
            story.ChangingNewValue = changingValue;
            story.ChangingUserGuid = userId;
            story.ChangingOldValue = changingOldValue;
            story.ChangingState = state;
            story.ChangiingTransactionDate = DateTime.Now;
            story.ChangingNumberCard = numberCard;

            _imEntites.AddToChangingStory(story);
            _imEntites.SaveChanges();
            return story.ChangingStoryId;
        }


        public Guid AddFinancialData(DateTime? financialDataDate, double? financialDataPrice, double? financialDataPriceWithUs, Guid financialDataRealtorId,
            string fnancialDataContract, string financialDataSource)
        {
            var data = new FinancialData();
            data.FinancialDataDate = financialDataDate;
            data.FinancialDataPrice = financialDataPrice;
            data.FinancialDataPriceWithUs = financialDataPriceWithUs;
            data.FinancialDataRealtorId = financialDataRealtorId;
            data.FnancialDataContract = fnancialDataContract;
            data.FinancialDataSource = financialDataSource;
            data.FinancialDataState = "Продам";

            _imEntites.AddToFinancialData(data);
            _imEntites.SaveChanges();
            return data.FinancialDataId;
        }

        public  Guid AddUserEntrance(Guid userId, DateTime? timeEnter, DateTime? timeExit)
        {
            var userEntrance = new UserEntrance();
            userEntrance.UserEntranceTimeEnter = timeEnter;
            userEntrance.UserEntranceTimeExit = timeExit;
            userEntrance.UserEntranceUserId = userId;
            _imEntites.AddToUserEntrance(userEntrance);
            _imEntites.SaveChanges();
            return userEntrance.UserEntranceId;

        }


        #endregion  ADD

        #region DELETE

        public void DeleteFlat(Guid? flatId)
        {
            var deleteFlat = _imEntites.Flat.Single(flat => flat.FlatId == flatId);
            _imEntites.Flat.DeleteObject(deleteFlat);
            _imEntites.SaveChanges();
        }

        public void DeleteNewFlat(Guid? newFlatId)
        {
            var deleteFlat = _imEntites.NewFlat.Single(flat => flat.NewFlatId == newFlatId);
            _imEntites.NewFlat.DeleteObject(deleteFlat);
            _imEntites.SaveChanges();
        }

        public void DeleteCommercialRealEstate(Guid? commercialRealEstateId)
        {
            var deleteEstate = _imEntites.CommercialRealEstate.Single(estate => estate.CommercialRealEstatetId  == commercialRealEstateId);
            _imEntites.CommercialRealEstate.DeleteObject(deleteEstate);
            _imEntites.SaveChanges();
        }

        public void DeletePlots(Guid? plotsId)
        {
            var deletePlots = _imEntites.Plots.Single(plots => plots.PlotsId == plotsId);
            _imEntites.Plots.DeleteObject(deletePlots);
            _imEntites.SaveChanges();
        }

        #endregion DELETE

        #region EDIT


        public void EditUser(Guid idTRealtorEdit, string firstName, string lastName,
                                                          string thirdName, string phone, string addPhone,
                                                          string advName, string email, string login,
                                                          string password)
        {

            var realtorToEdit = GetUserById(idTRealtorEdit);
            realtorToEdit.UserLogin = login;
            realtorToEdit.UserFirstName = firstName;
            realtorToEdit.UserLastName = lastName;
            realtorToEdit.UserThirdName = thirdName;
            realtorToEdit.UserPassword = password;
            realtorToEdit.UserEmail = email;
            realtorToEdit.UserOrganization = advName;
            realtorToEdit.UserPhoneNumber = addPhone;

            _imEntites.SaveChanges();
        }



        public void EditRealtor(Guid realtorId, string realtorFirstName, string realtorLastName, string realtorThirdName, string realtorPhone, string realtorAdditionalPhone, string realtorAdvName,
         string realtorEmail, string realtorUserName, string realtorPassword)
        {
            var realtorEdit = _imEntites.Realtor.Single(realtor => realtor.RealtorId == realtorId);

            realtorEdit.RealtorFirstName = realtorFirstName;
            realtorEdit.RealtorLastName = realtorLastName;
            realtorEdit.RealtorAdditionalPhone = realtorAdditionalPhone;
            realtorEdit.RealtorAdvName = realtorAdvName;
            realtorEdit.RealtorEmail = realtorEmail;
            realtorEdit.RealtorPassword = realtorPassword;
            realtorEdit.RealtorPhone = realtorPhone;
            realtorEdit.RealtorThirdName = realtorThirdName;
            realtorEdit.RealtorUserName = realtorUserName;

            _imEntites.SaveChanges();
        }

        public void EditFlat(Guid flatId, string flatAdditionalInformation,
          Guid flatBalconId, string flatAddress, string flatBoilerPosition, string flatDocumentsReady,
          Guid flatFinancialDataId, int? flatFloor, int? flatFloors, Guid flatHouseMaterial, double? flatKitchenArea,
          double? flatTotalArea, double? flatLivingArea, string flatObject, Guid flatRegionId, bool? flatMortgage,
          string flatNearestSigns, Guid flatOwnerId, string flatNote, int? flatRoomCount, int? flatRoomsToSell,
          Guid flatSellTermsId, int? flatNeighboursCount, int? flatNeighboursChildrenCount, int? flatNeighboursPensioneersCount,
          string flatShowTerms, DateTime? flatShowTime, bool? flatTelephone, Guid flatTypeId, double? flatTotalCost, double? flatCostPerMeter,
          Guid flatWCId, string flatIRR, string flatWindowsView, string flatHowToGetByPubTransport,
          bool? FlatRoomAppointment, string flatRoomPlanning, Guid flatNewId)
        {

            var flatEdit = _imEntites.Flat.Single(flat => flat.FlatId == flatId);
            flatEdit.FlatAdditionalInformation = flatAdditionalInformation;
            flatEdit.FlatBalconId = flatBalconId;
            flatEdit.FlatAddress = flatAddress;
            flatEdit.FlatBoilerPosition = flatBoilerPosition;
            flatEdit.FlatDocumentsReady = flatDocumentsReady;
            flatEdit.FlatFinancialDataId = flatFinancialDataId;
            flatEdit.FlatFloor = flatFloor;
            flatEdit.FlatFloors = flatFloors;
            flatEdit.FlatHouseMaterial = flatHouseMaterial;
            flatEdit.FlatKitchenArea = flatKitchenArea;
            flatEdit.FlatTotalArea = flatTotalArea;
            flatEdit.FlatLivingArea = flatLivingArea;
            flatEdit.FlatObject = flatObject;
            flatEdit.FlatRegionId = flatRegionId;
            flatEdit.FlatMortgage = flatMortgage;
            flatEdit.FlatNearestSigns = flatNearestSigns;
            flatEdit.FlatOwnerId = flatOwnerId;
            flatEdit.FlatNote = flatNote;
            flatEdit.FlatRoomCount = flatRoomCount;
            flatEdit.FlatRoomsToSell = flatRoomsToSell;
            flatEdit.FlatSellTermsId = flatSellTermsId;
            flatEdit.FlatNeighboursCount = flatNeighboursCount;
            flatEdit.FlatNeighboursChildrenCount = flatNeighboursChildrenCount;
            flatEdit.FlatNeighboursPensioneersCount = flatNeighboursPensioneersCount;
            flatEdit.FlatShowTerms = flatShowTerms;
            flatEdit.FlatShowTime = flatShowTime;
            flatEdit.FlatTelephone = flatTelephone;
            flatEdit.FlatTypeId = flatTypeId;
            flatEdit.FlatTotalCost = flatTotalCost;
            flatEdit.FlatCostPerMeter = flatCostPerMeter;
            flatEdit.FlatWCId = flatWCId;
            flatEdit.FlatIRR = flatIRR;
            flatEdit.FlatWindowsView = flatWindowsView;
            flatEdit.FlatHowToGetByPubTransport = flatHowToGetByPubTransport;
            flatEdit.FlatRoomAppointment = FlatRoomAppointment;
            flatEdit.FlatRoomPlanning = flatRoomPlanning;
            flatEdit.FlatNewId = flatNewId;


            _imEntites.SaveChanges();
        }


        public void EditNewFlat(Guid newFlatId,string newFlatAdditionalInfo, string newFlatAddress, Guid newFlatBalconTypeId,
            string newFlatBuildingStage, double? newFlatCostPerMeter, string newFlatDocuments, string newFlatFacingAdditionalInfo,
            int? newFlatFloor, int? newFlatFloorsCount, Guid newFlatFinancialDataId, double? newFlatTotalArea,
            double? newFlatLivingArea, double? newFlatKitchenArea, Guid newFlatHouseMaterialId, string newFlatObject,
            string newFlatObjectBuilder, string newFlatNote, Guid newFlatOwnerId, int? newFlatRoomsCount, string newFlatSellForm,
            string newFlatShowTerms, DateTime? newFlatShowTime, string newFlatBuildingFinishedDate, Guid newFlatTypeId, string newFlatIRR,
            Guid newFlatWCId, string newFlatWindowsView, string newFlatInfoForContactsWithSellers, Guid newFlatObjectRegionId,
            string newFlatObjectNearestSign, double? newFlatTotalCost, bool? newFlatFacingTypeBlack, string newFlatFacing, bool? newFlatAjoiningRoom,
            Guid newFlatNewId)
        {
              var newflatEdit = _imEntites.NewFlat.Single(flat => flat.NewFlatId == newFlatId);

            newflatEdit.NewFlatAdditionalInfo = newFlatAdditionalInfo;
            newflatEdit.NewFlatAddress = newFlatAddress;
            newflatEdit.NewFlatBalconTypeId = newFlatBalconTypeId;
            newflatEdit.NewFlatBuildingStage = newFlatBuildingStage;
            newflatEdit.NewFlatCostPerMeter = newFlatCostPerMeter;
            newflatEdit.NewFlatDocuments = newFlatDocuments;
            newflatEdit.NewFlatFacingAdditionalInfo = newFlatFacingAdditionalInfo;
            newflatEdit.NewFlatFloor = newFlatFloor;
            newflatEdit.NewFlatFloorsCount = newFlatFloorsCount;
            newflatEdit.NewFlatFinancialDataId = newFlatFinancialDataId;
            newflatEdit.NewFlatTotalArea = newFlatTotalArea;
            newflatEdit.NewFlatLivingArea = newFlatLivingArea;
            newflatEdit.NewFlatKitchenArea = newFlatKitchenArea;
            newflatEdit.NewFlatHouseMaterialId = newFlatHouseMaterialId;
            newflatEdit.NewFlatObject = newFlatObject;
            newflatEdit.NewFlatObjectBuilder = newFlatObjectBuilder;
            newflatEdit.NewFlatNote = newFlatNote;
            newflatEdit.NewFlatOwnerId = newFlatOwnerId;
            newflatEdit.NewFlatRoomsCount = newFlatRoomsCount;
            newflatEdit.NewFlatSellForm = newFlatSellForm;
                ////исправить
            newflatEdit.NewFlatShowTerms = newFlatShowTerms;
            newflatEdit.NewFlatShowTime = newFlatShowTime;
            newflatEdit.NewFlatBuildingFinishedDate = newFlatBuildingFinishedDate;
            newflatEdit.NewFlatTypeId = newFlatTypeId;
            newflatEdit.NewFlatIRR = newFlatIRR;
            newflatEdit.NewFlatWCId = newFlatWCId;
            newflatEdit.NewFlatWindowsView = newFlatWindowsView;
            newflatEdit.NewFlatInfoForContactsWithSellers = newFlatInfoForContactsWithSellers;
            newflatEdit.NewFlatObjectRegionId = newFlatObjectRegionId;
            newflatEdit.NewFlatObjectNearestSign = newFlatObjectNearestSign;
            newflatEdit.NewFlatTotalCost = newFlatTotalCost;
            newflatEdit.NewFlatFacingTypeBlack = newFlatFacingTypeBlack;
                //отделка
            newflatEdit.NewFlatFacing = newFlatFacing;
            newflatEdit.NewFlatAjoiningRoom = newFlatAjoiningRoom;
            newflatEdit.NewFlatNewId = newFlatNewId;

            _imEntites.SaveChanges();

        }


        public void EditCommercialRealEstate(Guid estateId, string commercialRealEstateAdditionalInformation, string commercialRealEstateAddress,
            bool? commercialRealEstateBaseFloor, Guid commercialRealEstateFinancialDataId, int? commercialRealEstateFloor, int? commercialRealEstateMaxFloors,
            Guid commercialRealEstateFloorMaterialId, bool? commercialRealEstateInternet, double? commercialRealEstateCellingHeigh,
            string commercialRealEstateEntrance, bool? commercialRealEstateEntranceSeparate, string commercialRealEstateObject,
            bool? commercialRealEstateHasTenants, string commercialRealEstateTenants, int? commercialRealEstateLineFromCentralStreet, DateTime? commercialRealEstatePutPeriodFrom,
            bool? commercialRealEstatePhone, string commercialRealEstateGeneralInformation, string commercialRealEstateCommunications, Guid commercialRealEstateOwnerId,
            Guid commercialRealEstateWCId, string commercialRealEstatePowers, Guid commercialRealEstateParkingTypeId, string commercialRealEstateRepair,
            int? commercialRealEstateRoomCount, Guid commercialRealEstateRegionId, string commercialRealEstateShowTerms, DateTime? commercialRealEstateShowTime,
            Guid commercialRealEstateWallMaterialId, double? commercialRealEstateTotalArea, string commercialRealEstatePremisesLocation,
            string commercialRealEstateWindows, DateTime? commercialRealEstatePutPeriodTo, Guid commercialRealEstateTypeId, string commercialRealEstatePlanning,
            string commercialRealEstateAppointment, Guid commercialNewGuid)
        {
            var newEstate = _imEntites.CommercialRealEstate.Single(estate => estate.CommercialRealEstatetId == estateId);

            newEstate.CommercialRealEstateAdditionalInformation =
                commercialRealEstateAdditionalInformation;
            newEstate.CommercialRealEstateAddress = commercialRealEstateAddress;
            newEstate.CommercialRealEstateBaseFloor = commercialRealEstateBaseFloor;
            newEstate.CommercialRealEstateFinancialDataId = commercialRealEstateFinancialDataId;
            newEstate.CommercialRealEstateFloor = commercialRealEstateFloor;
            newEstate.CommercialRealEstateMaxFloors = commercialRealEstateMaxFloors;
            newEstate.CommercialRealEstateFloorMaterialId = commercialRealEstateFloorMaterialId;
            newEstate.CommercialRealEstateInternet = commercialRealEstateInternet;
            newEstate.CommercialRealEstateCellingHeigh = commercialRealEstateCellingHeigh;
            newEstate.CommercialRealEstateEntrance = commercialRealEstateEntrance;
            newEstate.CommercialRealEstateEntranceSeparate =
                commercialRealEstateEntranceSeparate;
            newEstate.CommercialRealEstateObject = commercialRealEstateObject;
            newEstate.CommercialRealEstateHasTenants = commercialRealEstateHasTenants;
            newEstate.CommercialRealEstateTenants = commercialRealEstateTenants;
            newEstate.CommercialRealEstateLineFromCentralStreet =
                commercialRealEstateLineFromCentralStreet;
            newEstate.CommercialRealEstatePutPeriodFrom = commercialRealEstatePutPeriodFrom;
            newEstate.CommercialRealEstatePhone = commercialRealEstatePhone;
            newEstate.CommercialRealEstateGeneralInformation =
                commercialRealEstateGeneralInformation;
            newEstate.CommercialRealEstateCommunications = commercialRealEstateCommunications;
            newEstate.CommercialRealEstateOwnerId = commercialRealEstateOwnerId;
            newEstate.CommercialRealEstateWCId = commercialRealEstateWCId;
            newEstate.CommercialRealEstatePowers = commercialRealEstatePowers;
            newEstate.CommercialRealEstateParkingTypeId = commercialRealEstateParkingTypeId;
            newEstate.CommercialRealEstateRepair = commercialRealEstateRepair;
            newEstate.CommercialRealEstateRoomCount = commercialRealEstateRoomCount;
            newEstate.CommercialRealEstateRegionId = commercialRealEstateRegionId;
            newEstate.CommercialRealEstateShowTerms = commercialRealEstateShowTerms;
            newEstate.CommercialRealEstateShowTime = commercialRealEstateShowTime;
            newEstate.CommercialRealEstateWallMaterialId = commercialRealEstateWallMaterialId;
            newEstate.CommercialRealEstateTotalArea = commercialRealEstateTotalArea;
            newEstate.CommercialRealEstatePremisesLocation =
                commercialRealEstatePremisesLocation;
            newEstate.CommercialRealEstateWindows = commercialRealEstateWindows;
            newEstate.CommercialRealEstateWindows = commercialRealEstateWindows;
            newEstate.CommercialRealEstatePutPeriodTo = commercialRealEstatePutPeriodTo;
            newEstate.CommercialRealEstateTypeId = commercialRealEstateTypeId;
            newEstate.CommercialRealEstatePlanning = commercialRealEstatePlanning;
            newEstate.CommercialRealEstateAppointment = commercialRealEstateAppointment;
            newEstate.CommercialReaEstateNewId = commercialNewGuid;

            _imEntites.SaveChanges();

        }

        public void EditPlots(Guid plotsId, string plotsAdditionalInformation, string plotsAddress, double? plotsArea, string plotsBuildingRights,
            bool? plotsCommunicationElectricPower, bool? plotsCommunicationGasSupply, bool? plotsCommunicationSewage, bool? plotsCommunicationWater,
            double? plotsDistanceFromBusStop, double? plotsDistanceFromForest, double? plotsDistanceFromLake, double plotsDistanceFromRailwayStation,
            Guid plotsFinancialDataId, string plotsDocumentsReady, string plotsNearestSign, string plotsObject, Guid plotsOwnerId, Guid plotsRegionId,
            bool? plotsRegistration, string plotsPurpose, bool? plotsMastered, DateTime? plotsShowDate, Guid plotsSellTermsId, string plotsHowToGetByTransport,
            string plotsShowTerms, Guid plotNewGuid)
        {
             var plotsEdit = _imEntites.Plots.Single(plots => plots.PlotsId == plotsId);

            plotsEdit.PlotsAdditionalInformation = plotsAdditionalInformation;
            plotsEdit.PlotsAddress = plotsAddress;
            plotsEdit.PlotsArea = plotsArea;
            plotsEdit.PlotsBuildingRights = plotsBuildingRights;
            plotsEdit.PlotsCommunicationElectricPower = plotsCommunicationElectricPower;
            plotsEdit.PlotsCommunicationGasSupply = plotsCommunicationGasSupply;
            plotsEdit.PlotsCommunicationSewage = plotsCommunicationSewage;
            plotsEdit.PlotsCommunicationWater = plotsCommunicationWater;
            plotsEdit.PlotsDistanceFromBusStop = plotsDistanceFromBusStop;
            plotsEdit.PlotsDistanceFromForest = plotsDistanceFromForest;
            plotsEdit.PlotsDistanceFromLake = plotsDistanceFromLake;
            plotsEdit.PlotsDistanceFromRailwayStation = plotsDistanceFromRailwayStation;
            plotsEdit.PlotsFinancialDataId = plotsFinancialDataId;
            plotsEdit.PlotsDocumentsReady = plotsDocumentsReady;
            plotsEdit.PlotsNearestSign = plotsNearestSign;
            plotsEdit.PlotsObject = plotsObject;
            plotsEdit.PlotsOwnerId = plotsOwnerId;
            plotsEdit.PlotsRegionId = plotsRegionId;
            plotsEdit.PlotsRegistration = plotsRegistration;
            plotsEdit.PlotsPurpose = plotsPurpose;
            plotsEdit.PlotsMastered = plotsMastered;
            plotsEdit.PlotsShowDate = plotsShowDate;
            plotsEdit.PlotsSellTermsId = plotsSellTermsId;
            plotsEdit.PlotsHowToGetByTransport = plotsHowToGetByTransport;
            plotsEdit.PlotsShowTerms = plotsShowTerms;
            plotsEdit.PlotNewId = plotNewGuid;

            _imEntites.SaveChanges();
        }

        public void EditFinancialData(Guid financialDataId, DateTime? financialDataDate, double? financialDataPrice, double? financialDataPriceWithUs, Guid financialDataRealtorId,
          string fnancialDataContract, string source)
        {
            var data = _imEntites.FinancialData.Single(fdata => fdata.FinancialDataId == financialDataId);
            data.FinancialDataDate = financialDataDate;
            data.FinancialDataPrice = financialDataPrice;
            data.FinancialDataPriceWithUs = financialDataPriceWithUs;
            data.FinancialDataRealtorId = financialDataRealtorId;
            data.FnancialDataContract = fnancialDataContract;
            data.FinancialDataSource = source;
            _imEntites.SaveChanges();
        }

        public void EditOwner(Guid ownerId, string ownerHomePhone, string ownerMobilePhone, string ownerName)
        {
            var ownerEdit = _imEntites.Owner.Single(owner => owner.OwnerId == ownerId);
            ownerEdit.OwnerHomePhone = ownerHomePhone;
            ownerEdit.OwnerMobilePhone = ownerMobilePhone;
            ownerEdit.OwnerName = ownerName;

            _imEntites.SaveChanges();
        }

        #endregion EDIT

        #region GETALL

        public IEnumerable<User> GetAllUsers()
        {
            return _imEntites.User.AsEnumerable();
        }



        public IEnumerable<Plots> GetAllPlots()
        {
            return _imEntites.Plots.AsEnumerable();
        }

        public IEnumerable<CommercialRealEstateParking> GetAllParking()
        {
            return _imEntites.CommercialRealEstateParking.AsEnumerable();
        }

        public IEnumerable<CommercialRealEstateFloorMaterial> GetAllFloorType()
        {
            return _imEntites.CommercialRealEstateFloorMaterial.AsEnumerable();
        }

        public IEnumerable<CommercialRealEstateType> GetAllTypeEstate()
        {
            return _imEntites.CommercialRealEstateType.AsEnumerable();
        }

        public IEnumerable<CommercialRealEstateWallsMaterial> GetAllWallsMaterial()
        {
            return _imEntites.CommercialRealEstateWallsMaterial.AsEnumerable();
        }

        public IEnumerable<BalconType> GetAllBalconType()
        {
            return _imEntites.BalconType.AsEnumerable();
        }

        public IEnumerable<FlatType> GetAllFlatType()
        {
            return _imEntites.FlatType.AsEnumerable();
        }

        public IEnumerable<HouseMaterial> GetAllHouseMaterial()
        {
            return _imEntites.HouseMaterial.AsEnumerable();
        }

        public IEnumerable<Flat> GetAllFlats()
        {
            return _imEntites.Flat.AsEnumerable();
        }

        public IEnumerable<NewFlat> GetAllNewFlats()
        {
            return _imEntites.NewFlat.AsEnumerable();
        }

        public IEnumerable<SellTerms> GetAllSellTerms()
        {
            return _imEntites.SellTerms.AsEnumerable();
        }

        public IEnumerable<Region> GetAllRegions()
        {
            return _imEntites.Region.AsEnumerable();
        }

        public IEnumerable<CommercialRealEstate> GetAllCommercial()
        {
            return _imEntites.CommercialRealEstate.AsEnumerable();
        }

        public IEnumerable<WCType> GetAllWCType()
        {
            return _imEntites.WCType.AsEnumerable();
        }

        //public IEnumerable<SellTerms> GetAllFormSells()
        //{
        //    return _imEntites.SellTerms.AsEnumerable();
        //}

        public IEnumerable<Realtor> GetAllRealtors()
        {
            return _imEntites.Realtor.AsEnumerable();
        }
        
        public IEnumerable<Advertisement> GetAllAdvertisement()
        {
            return _imEntites.Advertisement.AsEnumerable();
        }
        public IEnumerable<UserEntrance> GetAllUserEntrance()
        {
            return _imEntites.UserEntrance.AsEnumerable();
        }


        #endregion GETALL

        #region GETBYID


        public User GetUserById(Guid? userGuid)
        {
            if (userGuid == null)
                return null;
            return _imEntites.User.Single(user => user.UserId == userGuid);
        }

        public User GetUserByGuid(Guid? userGuid)
        {
            if (userGuid == null)
                return null;
            //return _imEntites.User.Single(user=>user.UserId == userGuid);
            return _imEntites.User.Single(realtor => realtor.UserId == userGuid);
        }

        public Owner GetOwnerById(Guid? ownerId)
        {
            return _imEntites.Owner.Single(owner => owner.OwnerId == ownerId);
        }

        public Realtor GetRealtorById(Guid? realtorId)
        {
            return _imEntites.Realtor.Single(realtor => realtor.RealtorId == realtorId);
        }

        public Flat GetFlatById(Guid? flatId)
        {
            return _imEntites.Flat.Single(flat => flat.FlatId == flatId);
        }

        public NewFlat GetNewFlatById(Guid? newFlatId)
        {
            return _imEntites.NewFlat.Single(flat => flat.NewFlatId == newFlatId);
        }

        public CommercialRealEstate GetCommercialRealEstateById(Guid? estateId)
        {
            return _imEntites.CommercialRealEstate.Single(estate => estate.CommercialRealEstatetId == estateId);
        }

        public Plots GetPlotsById(Guid? plotsId)
        {
            return _imEntites.Plots.Single(plots => plots.PlotsId == plotsId);
        }

        public FinancialData GetFinancialDataById(Guid? financialId)
        {
            return _imEntites.FinancialData.Single(financialData => financialData.FinancialDataId == financialId);
        }

        #endregion GETBYID



        #region ACCEPT

        public void AcceptNewFlat(Guid? newFlatId, bool accepted)
        {
            var newFlat = GetNewFlatById(newFlatId);
            newFlat.NewFlatAccepted = accepted;
            _imEntites.SaveChanges();
        }

        public void AcceptPlot(Guid? plotId, bool accepted)
        {
            var plot = GetPlotsById(plotId);
            plot.PlotsAccepted = accepted;
            _imEntites.SaveChanges();
        }

        public void AcceptFlat(Guid? flatId, bool accepted)
        {
            var flat = GetFlatById(flatId);
            flat.FlatAccepted = accepted;
            _imEntites.SaveChanges();
        }

        public void AcceptCommercial(Guid? commercialId, bool accepted)
        {
            var commercial = GetCommercialRealEstateById(commercialId);
            commercial.CommercialReatEstateAccepted = accepted;
            _imEntites.SaveChanges();
        }

        #endregion

        #region SET_SHOWN
        public void SetShownNewFlat(Guid? newFlatId, bool show)
        {
            var newFlat = GetNewFlatById(newFlatId);
            newFlat.NewFlatIsShown = show;
            _imEntites.SaveChanges();
        }

        public void SetShownCommercial(Guid? commercialId, bool show)
        {
            var commercial = GetCommercialRealEstateById(commercialId);
            commercial.CommercialRealEstateIsShown = show;
            _imEntites.SaveChanges();
        }

        public void SetShownFlat(Guid? flatId, bool show)
        {
            var flat = GetFlatById(flatId);
            flat.FlatIsShown = show;
            _imEntites.SaveChanges();
        }

        public void SetShownPlot(Guid? plotID, bool show)
        {
            var plot = GetPlotsById(plotID);
            plot.PlotsIsShown = show;
            _imEntites.SaveChanges();
        }
        #endregion SET_SHOWN

        #region BREAK

        public void BreakNewFlatNewId(Guid? newFlatId)
        {
            var newFlat = GetNewFlatById(newFlatId);
            newFlat.NewFlatNewId = null;
            _imEntites.SaveChanges();
        }

        public void BreakCommerciaNewId(Guid? commercialId)
        {
            var commercial = GetCommercialRealEstateById(commercialId);
            commercial.CommercialReaEstateNewId = null;
            _imEntites.SaveChanges();
        }

        public void BreakFlatNewId(Guid? flatId)
        {
            var flat = GetFlatById(flatId);
            flat.FlatNewId = null;
            _imEntites.SaveChanges();
        }

        public void BreakPlotNewId(Guid? plotId)
        {
            var plot = GetPlotsById(plotId);
            plot.PlotNewId = null;
            _imEntites.SaveChanges();
        }
        #endregion

    }
}
